<%@page import="org.apache.tomcat.jni.Mmap"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page
	import="com.sun.org.apache.xml.internal.resolver.readers.CatalogReader"%>
<%@page
	import="loginmysql.*,java.util.*, logic.*,java.text.*,java.sql.DriverManager,
java.sql.ResultSet,java.sql.SQLException,java.util.ArrayList"%>
<%@ page import="java.io.*,javax.mail.*"%>
<%@ page import="javax.mail.internet.*,javax.activation.*"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%!String result="Ocurri&oacute; un error. Intente de nuevo.";

	String mssgStart = "<html><body>";
	String mssgHead = "";
	String localidad = "";
	String getLoc;
	String tableHead;
	String mssgEnd;
	String mssgItems = "";
	String mssgEnc;

	// Defining properties%>

<%
	//String strCount =(String) session.getAttribute("numItems");

	//session = request.getSession(false);
	if (session == null || session.getAttribute("loggedIn") == null) {
		//not logged
%><a href="FormLogin.jsp">Inicie Sesion</a>
<%
	} else {

		//loggeds
		String strTurno = request.getParameter("turno");
		String strSalida = request.getParameter("salida");
		String strControl = request.getParameter("control");
		String fecha = (String) session.getAttribute("fecha");

		ArrayList<Diferencia> difs = new ArrayList<Diferencia>();

		String unidad = request.getParameter("unidades");

		int numUnidad = Integer.parseInt(unidad);

		/*
		 * String tipo = request.getParameter("tipo");
		 * 
		 * int numTipo = Integer.parseInt(tipo);
		 */

		String loc = request.getParameter("localidad");
		int numLoc = Integer.parseInt(loc);

		// System.out.println(strTurno);

		int FormId = 0;

		int FuncId = (int) session.getAttribute("FuncId");

		java.sql.Connection conn = null;
		java.sql.Statement stmt = null;
		java.sql.Statement stmt2 = null;
		PreparedStatement prep = null;
		String sql;

		// JDBC driver name and database URL
		final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
		// static final String DB_URL = "jdbc:mysql://localhost/Cot";
		final String DB_URL = "jdbc:mysql://localhost/cot";
		// Database credentials
		// static final String USER = "root";
		final String USER = "root";
		final String PASS = "Beyer9499";

		try {
			Class.forName("com.mysql.jdbc.Driver");

			// STEP 3: Open a connection
			System.out.print("\nConnecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			System.out.println(" SUCCESS!\n");

			// STEP 5: Excute query
			System.out.print("\nInserting records into table...");
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();

			// response.setBufferSize(8192 * 16);

			conn.setAutoCommit(false);
			conn.setTransactionIsolation(conn.TRANSACTION_READ_UNCOMMITTED);

			sql = "select formid, HoraSubmit, funcid, uid from form where (HoraSubmit > (now() - interval 30 minute))and uid="
					+ numUnidad + " and funcid=" + FuncId;
			ResultSet rs3 = stmt.executeQuery(sql);
			if (rs3.next()) {// checkea si ya fue ingresado
				result = "Este formulario ya fue ingresado en los anteriores 30 minutos";
				session.setAttribute("result", result);
				
				System.out.println("ya se mando " + result);
				throw new Exception("Formulario ya enviado");
			}

			SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");

			// este metodo crea un formulario y le asigna los datos
			final String OLD_FORMAT = "dd/MM/yyyy";
			final String NEW_FORMAT = "yyyy/MM/dd";

			SimpleDateFormat dateFormat = new SimpleDateFormat(
					OLD_FORMAT);
			Date d = dateFormat.parse(fecha);
			dateFormat.applyPattern(NEW_FORMAT);
			String strFecha = dateFormat.format(d);
			sql = "Insert into form (Fecha, HoraControl, Uid, FuncId, lid) values ('"
					+ strFecha
					+ "','"
					+ strControl
					+ "',"
					+ numUnidad
					+ "," + FuncId + "," + numLoc + ")";

			stmt.executeUpdate(sql, stmt.RETURN_GENERATED_KEYS);

			int autoIncKeyFromApi = -1;

			ResultSet rs = stmt.getGeneratedKeys();

			if (rs.next()) {
				autoIncKeyFromApi = rs.getInt(1);
			} else {

				// throw an exception from here
			}

			System.out.println("Key returned from getGeneratedKeys():"
					+ autoIncKeyFromApi);

			sql = "SELECT FormId FROM form";
			rs3 = stmt.executeQuery(sql);
			if (rs3.last()) {
				FormId = rs3.getInt("FormId");

			}
			/*
			 * ResultSet rs =
			 * stmt.executeQuery("select last_insert_id() as last_id from form"
			 * ); FuncId = rs.getInt("last_id");
			 */
			// out.println(autoIncKeyFromApi);

			ArrayList<Categoria> cats = (ArrayList<Categoria>) session
					.getAttribute("catArray");
			// int FormId = MysqlLogin.getFormId();
			for (Categoria cat : cats) { // este metodo crea los item data y los
											// asigna a el formulario
				ArrayList<Item> items = cat.getItems();
				for (Item item : items) {
					int radioButtonState = Integer.parseInt(request
							.getParameter(item.getGroup()));
					String strObs = request.getParameter(item.getObs());

					String itemInfo = item.getInfo();
					String itemObs = strObs;
					/*
					 * String sql2 =
					 * "select Fecha, yn, iid from itemdata, form where fid=(select formid from form where uid=? order by horasubmit desc limit 1,1)and iid=? order by idid asc"
					 * ; System.out.println(numUnidad+" "+item.getId()); prep =
					 * conn.prepareStatement(sql2);
					 * 
					 * prep.setInt(1, numUnidad); prep.setInt(2, item.getId());
					 * rs = prep.executeQuery();
					 */

					if (radioButtonState > 0) {// genera el html a mandar por
												// mail
						// out.println(item.getInfo() + " esta mal<br>");
						Diferencia dif = new Diferencia(
								radioButtonState, strFecha, numUnidad,
								itemObs, itemInfo);
						difs.add(dif);
					}

					/*
					 * if(rs.next()){ System.out.println("hay rss"); int yesno =
					 * rs.getInt("yn"); String lastFecha =
					 * rs.getString("fecha"); if(yesno==1){//si el anterior
					 * estaba bien y el nuevo roto
					 * //out.println(itemInfo+" estaba "
					 * +yesno+" ahora esta "+radioButtonState); Diferencia dif =
					 * new Diferencia(lastFecha, yesno,
					 * radioButtonState,strFecha, numUnidad,itemObs);
					 * difs.add(dif); } } else{ if(rs.next()){
					 * System.out.println("hay rss"); int yesno =
					 * rs.getInt("yn"); String lastFecha =
					 * rs.getString("fecha"); if(yesno==0){//si el anterior
					 * estaba roto y el nuevo bien
					 * //out.println(itemInfo+" estaba "
					 * +yesno+" ahora esta "+radioButtonState); Diferencia dif =
					 * new Diferencia(yesno, radioButtonState,strFecha,
					 * numUnidad,itemObs, itemInfo); difs.add(dif); } } } todo
					 * esto lo hice al pedo
					 */

					String sql2 = "Insert into itemdata (yn, Fid, Iid) values('"
							+ radioButtonState
							+ "','"
							+ autoIncKeyFromApi
							+ "','"
							+ item.getId()
							+ "')";
					stmt2.executeUpdate(sql2);
					System.out.println("in for");

					if (strObs.isEmpty()) {// si no hay observacion

					} else {
						String sql3 = "Insert into observ values(LAST_INSERT_ID(),'"
								+ strObs + "')";
						stmt2.executeUpdate(sql3);

					}
				}
			}

			
			mssgStart = "<html><body>";
			localidad = "";
			getLoc = "Select info from localidad where lid=?";
			tableHead = "<table border='1'><tr><td>Item</td><td>Observaci&oacute;n</td></tr>";
			mssgEnd = "</table></body></html>";
			mssgItems = "";

			prep = conn.prepareStatement(getLoc);
			prep.setInt(1, numLoc);

			rs = prep.executeQuery();

			if (rs.next()) {
				localidad = rs.getString("info");
			}

			for (Diferencia difer : difs) {
				mssgItems += "<tr><td>" + difer.getItemInfo()
						+ "</td><td>" + difer.getObs() + "</td></tr>";
			}
			/*
			 * String strTipo; if (numTipo == 0) { strTipo = "Entrada"; } else {
			 * strTipo = "Salida"; }
			 */

			mssgEnc = /*
						 * "<p>Inspeccion de: " + strTipo + "</p>"+
						 */"<p>D&iacute;a: " + strFecha
					+ "</p><p> Lugar: " + localidad
					+ "</p><p>Realizada por: "
					+ session.getAttribute("FullName")
					+ "</p><p>Hora de Control: " + strControl + "</p>";

			if (difs.size() == 0) {// no hay diferencias, no hacer nada

			} else if (difs.size() == 1) {
				mssgHead = "<p> Se encontr&oacute; el siguiente item insatisfactorio:</p>";
				result = MailSender.sendEmail(mssgStart + mssgEnc
						+ mssgHead + tableHead + mssgItems + mssgEnd);
			} else if (difs.size() > 1) {
				mssgHead = "<p> Se encontraron los siguientes items insatisfactorios: </p>";
				result = MailSender.sendEmail(mssgStart + mssgEnc
						+ mssgHead + tableHead + mssgItems + mssgEnd);
			}

			// messg = mssgStart + mssgEnc + mssgHead + mssgItems+ mssgEnd;

			

			System.out.println("resultado " + result);
			conn.commit();

		} catch (SQLException se) {
			conn.rollback();
			se.printStackTrace();
			result = "Hubo un problema con la base de datos, de conexi&oacute;n o puede haber ingresado un dato erroneo.";
			session.setAttribute("result", result);
			
			System.out.println("NO SUCCESS!\n");
		} catch (Exception e) {
			conn.rollback();
			session.setAttribute("result", result);
			
			System.out.println("lo catchee");
			e.printStackTrace();
		} finally {
			session.setAttribute("result", result);
			response.sendRedirect("SalidasForm.jsp");
			try {
				if (stmt != null)
					conn.close();
			} catch (SQLException se) {
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
	}
%>